const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 从环境变量或配置获取数据库连接信息
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  port: process.env.DB_PORT || 3306,
  charset: 'utf8mb4'
};

async function createDatabase() {
  try {
    const connection = await mysql.createConnection(dbConfig);
    
    // 创建数据库
    await connection.execute(`
      CREATE DATABASE IF NOT EXISTS blog 
      CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
    `);
    
    console.log('✅ 数据库 blog 创建成功');
    await connection.end();
  } catch (error) {
    console.error('❌ 数据库创建失败:', error.message);
    process.exit(1);
  }
}

async function initTables() {
  try {
    // 使用blog数据库
    const connection = await mysql.createConnection({
      ...dbConfig,
      database: 'blog'
    });

    // 读取表结构文件
    const schemaPath = path.join(__dirname, '..', 'lib', 'db.js');
    const schemaContent = fs.readFileSync(schemaPath, 'utf8');
    
    // 提取CREATE TABLE语句（简化版）
    const createTables = [
      `CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_name VARCHAR(50) NOT NULL UNIQUE,
        email VARCHAR(100) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        avatar VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS article (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        content TEXT,
        classify VARCHAR(50),
        subset_id INT,
        cover VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS comment (
        id INT AUTO_INCREMENT PRIMARY KEY,
        article_id INT NOT NULL,
        name VARCHAR(50),
        email VARCHAR(100),
        content TEXT,
        isread TINYINT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS file (
        id INT AUTO_INCREMENT PRIMARY KEY,
        filename VARCHAR(255) NOT NULL,
        original_name VARCHAR(255),
        url VARCHAR(500) NOT NULL,
        size BIGINT,
        type VARCHAR(50),
        subset_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS subset (
        id INT AUTO_INCREMENT PRIMARY KEY,
        subset_name VARCHAR(100) NOT NULL,
        classify VARCHAR(50),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS diary (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255),
        content TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS label (
        id INT AUTO_INCREMENT PRIMARY KEY,
        label_name VARCHAR(50) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS praise (
        id INT AUTO_INCREMENT PRIMARY KEY,
        article_id INT NOT NULL,
        user_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS message (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50),
        email VARCHAR(100),
        content TEXT,
        isread TINYINT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )`
    ];

    // 执行创建表
    for (const sql of createTables) {
      await connection.execute(sql);
    }
    
    console.log('✅ 所有数据表创建成功');
    await connection.end();
  } catch (error) {
    console.error('❌ 数据表创建失败:', error.message);
    process.exit(1);
  }
}

// 主函数
async function init() {
  console.log('🚀 开始初始化数据库...');
  
  await createDatabase();
  await initTables();
  
  console.log('🎉 数据库初始化完成！');
}

// 如果直接运行此脚本
if (require.main === module) {
  init();
}

module.exports = { createDatabase, initTables };